﻿CREATE procedure [dbo].[spReportClientVariance]
	@yearPosition int,
	@date datetime
AS
DECLARE @year int
SET @year = YEAR(GETDATE()) + @yearPosition
set ansi_nulls OFF

DECLARE @lastDatePrevious date, @currentDate varchar(10), @previousDate varchar(10), @yearText varchar(4), @weekDay int
DECLARE @query nvarchar(max)

SET @weekDay = DATEPART(weekday, @date)
SET @lastDatePrevious = DATEADD(DAY, CASE @weekDay WHEN 2 THEN -3 ELSE -1 END, @date)

SET @currentDate = CONVERT(varchar(10), @date, 103)
SET @previousDate = CONVERT(varchar(10), @lastDatePrevious, 103)
SET @yearText = CONVERT(varchar(4), @year)

DECLARE @MondayDate date
SELECT @MondayDate = DATEADD(DAY, -@weekDay + 2, CONVERT(date, @date))

DECLARE @value1 VARCHAR(1), @value2 VARCHAR(1)
SET @value1 = CONVERT(varchar(1), @weekDay - 2)
SET @value2 = CONVERT(varchar(1), @weekDay - CASE @weekDay WHEN 2 THEN 2 ELSE 1 END)
 
SET @query = 'SELECT DISTINCT ' +
	@yearText + '  as PeriodYear
	, ProgramId as [Program Id]
	, ProgramName as [Program Name]
	, ProgramManager as [Program Manager]
	, ProjectId as [Project Id]
	, ProjectName as [Project Name]
    , BowFlag as [Bow Flag]
	, ClientLevel5 as [Client Level 5]
	, ClientLevel6 as [Client Level 6]
	, ClientLevel7 as [Client Level 7]
	, Goc.GocAttributes as [Client GOC]
    , LobParent as [Lob Parent]
	, CONVERT(DECIMAL(38,2), ISNULL(PLFY' + @value1 + ', 0)) as [PreviousFY]
	, CONVERT(DECIMAL(38,2), ISNULL(Allocation' + @value1 + ', 0)) as [' + @previousDate + ']
	, 0 as [PreviousVariance]
	, CONVERT(DECIMAL(38,2), ISNULL(PLFY' + @value2 + ', 0)) as [CurrentFY]
	, CONVERT(DECIMAL(38,2), ISNULL(Allocation' + @value2 + ', 0)) as [' + @currentDate + ']
	, CONVERT(DECIMAL(38,2), ISNULL(Allocation' + @value2 + ', 0)) - CONVERT(DECIMAL(38,2), ISNULL(Allocation' + @value1 + ', 0)) as Variance
FROM BookOfWorkClientVariance variance
INNER JOIN GOC as GOC on Goc.GocAttributes = ClientGocCode AND Goc.ManagedGeography = ''BR''
Where PeriodYear = ' + @yearText + '
	AND variance.ReferenceDate = ''' + CONVERT(varchar(100), @MondayDate) + '''
	AND PLFY' + @value1 + ' IS NOT NULL AND PLFY' + @value2 + ' IS NOT NULL'

EXEC(@query)